package com.css.fxfzypg.modules.resultupload.repository.impl;

import com.css.fxfzypg.modules.preassesstaskmanage.vo.PreAssessVo;
import com.css.fxfzypg.modules.resultManagement.reportManagement.VO.ResultMainEntityVOV2;
import com.css.fxfzypg.modules.resultupload.repository.ResultTotalRepository;
import com.css.fxfzypg.modules.resultupload.repository.entity.PrefectureResultEntity;
import com.css.fxfzypg.modules.resultupload.repository.entity.ResultDetailsEntity;
import com.css.fxfzypg.modules.resultupload.repository.entity.ResultEntity;
import com.css.fxfzypg.modules.resultupload.repository.entity.ResultMainEntity;
import com.css.fxfzypg.modules.resultupload.vo.ResultMainEntityVO;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: fxfzypg
 * @Author: lhl
 * @CreateDate: 2022/11/3 9:20
 */
@Repository
public class ResultTotalRepositoryImpl implements ResultTotalRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public int saveResultTotal(ResultMainEntity entity) {
        String sql = "insert into ypg_result_total(task_id,sort,longitude,latitude,macro_position,magnitude,"
                + "disaster_area,disaster_num,disaster_gdp,death_toll_before,del_flag,create_name,create_id,"
                + "create_time,death_toll_after,data_source,stretcher_num_before,id,disaster_loss_before,"
                + "injured_num_before,resettled_num_before,rescue_force_before,tent_num_before,disaster_loss_after,"
                + "injured_num_after,resettled_num_after,rescue_force_after,tent_num_after,rescue_force_zy_before,"
                + "rescue_force_mj_before,rescue_force_qt_before,rescue_force_zy_after,rescue_force_mj_after,"
                + "rescue_force_qt_after,quilts_num_before,foods_num_before,water_num_before,bathroom_num_before,"
                + "showers_num_before,medical_matters_before,medical_num_before,nurse_num_before,psychologist_before,"
                + "quilts_num_after,foods_num_after,water_num_after,bathroom_num_after,showers_num_after,"
                + "medical_matters_after,medical_num_after,nurse_num_after,psychologist_after,sickroom_num_before,"
                + "sickbed_num_before,plasma_num_before,sickroom_num_after,sickbed_num_after,plasma_num_after,"
                + "stretcher_num_after)"
                + "values(:taskId,:sort,:longitude,:latitude,:macroPosition,:magnitude,:disasterArea,:disasterNum,"
                + ":disasterGdp,:deathTollBefore,:delFlag,:createName,:createId,:createTime,:deathTollAfter,"
                + ":dataSource,:stretcherNumBefore,:id,:disasterLossBefore,:injuredNumBefore,:resettledNumBefore,"
                + ":rescueForceBefore,:tentNumBefore,:disasterLossAfter,:injuredNumAfter,:resettledNumAfter,"
                + ":rescueForceAfter,:tentNumAfter,:rescueForceZyBefore,:rescueForceMjBefore,:rescueForceQtBefore,"
                + ":rescueForceZyAfter,:rescueForceMjAfter,:rescueForceQtAfter,:quiltsNumBefore,:foodsNumBefore,"
                + ":waterNumBefore,:bathroomNumBefore,:showersNumBefore,:medicalMattersBefore,:medicalNumBefore,"
                + ":nurseNumBefore,:psychologistBefore,:quiltsNumAfter,:foodsNumAfter,:waterNumAfter,:bathroomNumAfter,"
                + ":showersNumAfter,:medicalMattersAfter,:medicalNumAfter,:nurseNumAfter,:psychologistAfter,"
                + ":sickroomNumBefore,:sickbedNumBefore,:plasmaNumBefore,:sickroomNumAfter,:sickbedNumAfter,"
                + ":plasmaNumAfter,:stretcherNumAfter)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        // 拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public List<ResultMainEntity> getResultTotalByTaskId(String taskId, String dataSource) {
        String sql = "select * from ypg_result_total where del_flag='0' and task_id=?";
        if (PlatformObjectUtils.isNotEmpty(dataSource)) {
            sql = sql + " and data_source='" + dataSource + "'";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultMainEntity.class), taskId);
    }

    @Override
    public int updateResultTotal(ResultMainEntity entity) {
        StringBuilder sql = new StringBuilder("update ypg_result_total set data_source=:dataSource");
        if (PlatformObjectUtils.isNotEmpty(entity.getDeathTollBefore())) {
            sql.append(",death_toll_before=:deathTollBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getDeathTollAfter())) {
            sql.append(",death_toll_after=:deathTollAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getStretcherNumBefore())) {
            sql.append(",stretcher_num_before=:stretcherNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getStretcherNumAfter())) {
            sql.append(",stretcher_num_after=:stretcherNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getDisasterLossBefore())) {
            sql.append(",disaster_loss_before=:disasterLossBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getInjuredNumBefore())) {
            sql.append(",injured_num_before=:injuredNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getResettledNumBefore())) {
            sql.append(",resettled_num_before=:resettledNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceBefore())) {
            sql.append(",rescue_force_before=:rescueForceBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getTentNumBefore())) {
            sql.append(",tent_num_before=:tentNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getDisasterLossAfter())) {
            sql.append(",disaster_loss_after=:disasterLossAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getInjuredNumAfter())) {
            sql.append(",injured_num_after=:injuredNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getResettledNumAfter())) {
            sql.append(",resettled_num_after=:resettledNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceAfter())) {
            sql.append(",rescue_force_after=:rescueForceAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getTentNumAfter())) {
            sql.append(",tent_num_after=:tentNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceZyBefore())) {
            sql.append(",rescue_force_zy_before=:rescueForceZyBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceMjBefore())) {
            sql.append(",rescue_force_mj_before=:rescueForceMjBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceQtBefore())) {
            sql.append(",rescue_force_qt_before=:rescueForceQtBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceZyAfter())) {
            sql.append(",rescue_force_zy_after=:rescueForceZyAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceMjAfter())) {
            sql.append(",rescue_force_mj_after=:rescueForceMjAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceQtAfter())) {
            sql.append(",rescue_force_qt_after=:rescueForceQtAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getQuiltsNumBefore())) {
            sql.append(",quilts_num_before=:quiltsNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getFoodsNumBefore())) {
            sql.append(",foods_num_before=:foodsNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getWaterNumBefore())) {
            sql.append(",water_num_before=:waterNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getBathroomNumBefore())) {
            sql.append(",bathroom_num_before=:bathroomNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getShowersNumBefore())) {
            sql.append(",showers_num_before=:showersNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getMedicalMattersBefore())) {
            sql.append(",medical_matters_before=:medicalMattersBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getMedicalNumBefore())) {
            sql.append(",medical_num_before=:medicalNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getNurseNumBefore())) {
            sql.append(",nurse_num_before=:nurseNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getPsychologistBefore())) {
            sql.append(",psychologist_before=:psychologistBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getQuiltsNumAfter())) {
            sql.append(",quilts_num_after=:quiltsNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getFoodsNumAfter())) {
            sql.append(",foods_num_after=:foodsNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getWaterNumAfter())) {
            sql.append(",water_num_after=:waterNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getBathroomNumAfter())) {
            sql.append(",bathroom_num_after=:bathroomNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getShowersNumAfter())) {
            sql.append(",showers_num_after=:showersNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getMedicalMattersAfter())) {
            sql.append(",medical_matters_after=:medicalMattersAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getMedicalNumAfter())) {
            sql.append(",medical_num_after=:medicalNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getNurseNumAfter())) {
            sql.append(",nurse_num_after=:nurseNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getPsychologistAfter())) {
            sql.append(",psychologist_after=:psychologistAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getSickroomNumBefore())) {
            sql.append(",sickroom_num_before=:sickroomNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getSickbedNumBefore())) {
            sql.append(",sickbed_num_before=:sickbedNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getPlasmaNumBefore())) {
            sql.append(",plasma_num_before=:plasmaNumBefore");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getSickroomNumAfter())) {
            sql.append(",sickroom_num_after=:sickroomNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getSickbedNumAfter())) {
            sql.append(",sickbed_num_after=:sickbedNumAfter");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getPlasmaNumAfter())) {
            sql.append(",plasma_num_after=:plasmaNumAfter");
        }
        sql.append(",update_id=:updateId,update_time=:updateTime ");
        sql.append(" where task_id=:taskId and sort=:sort and magnitude=:magnitude");
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        // 拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql.toString(), source, keyHolder);
        return i;
    }

    @Override
    public int delResultTotalByTaskId(String taskId, String dataSource) {
        String sql = "delete from ypg_result_total where task_id =?";
        if (PlatformObjectUtils.isNotEmpty(dataSource)) {
            sql = sql + " and data_source='" + dataSource + "'";
        }
        return jdbcTemplate.update(sql, taskId);
    }

    @Override
    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public int saveResultDetailsEntity(ResultDetailsEntity entity) {
        String sql = "insert into ypg_result_total_details(task_id,sort,longitude,latitude,macro_position,magnitude,"
                + "angle,disaster_area,disaster_num,disaster_gdp,death_toll,del_flag,create_name,create_id,create_time,"
                + "data_source,stretcher_num,id,disaster_loss,injured_num,resettled_num,rescue_force,tent_num,"
                + "rescue_force_zy,rescue_force_mj,rescue_force_qt,quilts_num,foods_num,water_num,bathroom_num,"
                + "showers_num,medical_matters,medical_num,nurse_num,psychologist,sickroom_num,sickbed_num,plasma_num)"
                + "values(:taskId,:sort,:longitude,:latitude,:macroPosition,:magnitude,:angle,:disasterArea,"
                + ":disasterNum,:disasterGdp,:deathToll,:delFlag,:createName,:createId,:createTime,:dataSource,"
                + ":stretcherNum,:id,:disasterLoss,:injuredNum,:resettledNum,:rescueForce,:tentNum,:rescueForceZy,"
                + ":rescueForceMj,:rescueForceQt,:quiltsNum,:foodsNum,:waterNum,:bathroomNum,:showersNum,"
                + ":medicalMatters,:medicalNum,:nurseNum,:psychologist,:sickroomNum,:sickbedNum,:plasmaNum)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        // 拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public List<ResultDetailsEntity> getResultDetailsEntityByTaskId(
            String taskId, String dataSource) {
        String sql = "select * from ypg_result_total_details where del_flag='0' and task_id=?";
        if (PlatformObjectUtils.isNotEmpty(dataSource)) {
            sql = sql + " and data_source='" + dataSource + "'";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultDetailsEntity.class), taskId);
    }

    @Override
    public int updateResultDetailsEntity(ResultDetailsEntity entity) {
        StringBuilder sql = new StringBuilder("update ypg_result_total_details set data_source=:dataSource");
        if (PlatformObjectUtils.isNotEmpty(entity.getDeathToll())) {
            sql.append(",death_toll=:deathToll");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getStretcherNum())) {
            sql.append(",stretcher_num=:stretcherNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getDisasterLoss())) {
            sql.append(",disaster_loss=:disasterLoss");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getInjuredNum())) {
            sql.append(",injured_num=:injuredNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getResettledNum())) {
            sql.append(",resettled_num=:resettledNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForce())) {
            sql.append(",rescue_force=:rescueForce");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getTentNum())) {
            sql.append(",tent_num=:tentNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceZy())) {
            sql.append(",rescue_force_zy=:rescueForceZy");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceMj())) {
            sql.append(",rescue_force_mj=:rescueForceMj");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getRescueForceQt())) {
            sql.append(",rescue_force_qt=:rescueForceQt");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getQuiltsNum())) {
            sql.append(",quilts_num=:quiltsNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getFoodsNum())) {
            sql.append(",foods_num=:foodsNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getWaterNum())) {
            sql.append(",water_num=:waterNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getBathroomNum())) {
            sql.append(",bathroom_num=:bathroomNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getShowersNum())) {
            sql.append(",showers_num=:showersNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getMedicalMatters())) {
            sql.append(",medical_matters=:medicalMatters");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getMedicalNum())) {
            sql.append(",medical_num=:medicalNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getNurseNum())) {
            sql.append(",nurse_num=:nurseNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getPsychologist())) {
            sql.append(",psychologist=:psychologist");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getSickroomNum())) {
            sql.append(",sickroom_num=:sickroomNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getSickbedNum())) {
            sql.append(",sickbed_num=:sickbedNum");
        }
        if (PlatformObjectUtils.isNotEmpty(entity.getPlasmaNum())) {
            sql.append(",plasma_num=:plasmaNum");
        }
        sql.append(",update_id=:updateId,update_time=:updateTime ");
        sql.append(" where task_id=:taskId and sort=:sort and magnitude=:magnitude and angle=:angle");
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        // 拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql.toString(), source, keyHolder);
        return i;
    }

    @Override
    public int delResultDetailsEntityByTaskId(String taskId, String dataSource) {
        String sql = "delete from ypg_result_total_details where task_id =?";
        if (PlatformObjectUtils.isNotEmpty(dataSource)) {
            sql = sql + " and data_source='" + dataSource + "'";
        }
        return jdbcTemplate.update(sql, taskId);
    }

    @Override
    public ResultDetailsEntity getResultDetailsEntity(String taskId, int faultNum, BigDecimal earthquakeLevel, int angle) {
        String sql = "select * from ypg_result_total_details where del_flag='0' and task_id='" + taskId
                + "' and sort='" + faultNum + "' and magnitude='" + earthquakeLevel + "' and " + "angle='" + angle + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResultDetailsEntity.class));
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public List<ResultDetailsEntity> getResultDetailsEntityByTaskId(String taskId) {
        String sql = "select * from ypg_result_total_details where del_flag='0' and task_id=?" +
                " order by cast(sort as int),magnitude,angle";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultDetailsEntity.class), taskId);
    }

    @Override
    public List<ResultMainEntity> getResultTotalByTaskId(String taskId) {
        String sql = "select * from ypg_result_total where del_flag='0' and task_id=?" +
                " order by cast(sort as int),magnitude";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultMainEntity.class), taskId);
    }

    @Override
    public List<ResultMainEntity> getResultTotalByTaskId(List<String> taskIdList) {
        String sql = "select * from ypg_result_total where del_flag='0' and task_id in (:taskIdList)" +
                " order by cast(sort as int),magnitude";
        Map idMap = new HashMap();
        idMap.put("taskIdList", taskIdList);
        return namedParameterJdbcTemplate.query(sql, idMap, new BeanPropertyRowMapper<>(ResultMainEntity.class));
    }

    @Override
    public Map<String,Object> getResultTotalByTaskId(String taskId, Integer curPage, Integer pageSize) {
        StringBuilder sql = new StringBuilder("select id,task_id,sort,longitude,latitude,macro_position,magnitude,disaster_area," +
                "disaster_num,disaster_gdp,death_toll_before,del_flag,create_name,create_id,create_time,death_toll_after," +
                "data_source,stretcher_num_before,disaster_loss_before,injured_num_before,resettled_num_before,rescue_force_before,stretcher_num_after," +
                "tent_num_before,disaster_loss_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after," +
                "rescue_force_zy_before,rescue_force_mj_before,rescue_force_qt_before,rescue_force_zy_after,rescue_force_mj_after," +
                "rescue_force_qt_after,quilts_num_before,foods_num_before,water_num_before,bathroom_num_before,showers_num_before," +
                "medical_matters_before,medical_num_before,nurse_num_before,psychologist_before,quilts_num_after,foods_num_after," +
                "water_num_after,bathroom_num_after,showers_num_after,medical_matters_after,medical_num_after,nurse_num_after,psychologist_after," +
                "sickroom_num_before,sickbed_num_before,plasma_num_before,sickroom_num_after,sickbed_num_after,plasma_num_after " +
                "from ypg_result_total where del_flag='0' and task_id=?" +
                " order by cast(sort as int),magnitude");
        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);

        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResultMainEntity.class), taskId).size();
        List<ResultMainEntity> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ResultMainEntity.class),taskId);

        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",list);
        return map;
    }

    @Override
    public List<ResultMainEntity> getAllByTaskId(String taskId) {
        StringBuilder sql = new StringBuilder("select id,task_id,sort,longitude,latitude,macro_position,magnitude,disaster_area," +
                "disaster_num,disaster_gdp,death_toll_before,del_flag,create_name,create_id,create_time,death_toll_after," +
                "data_source,stretcher_num_before,disaster_loss_before,injured_num_before,resettled_num_before,rescue_force_before,stretcher_num_after," +
                "tent_num_before,disaster_loss_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after," +
                "rescue_force_zy_before,rescue_force_mj_before,rescue_force_qt_before,rescue_force_zy_after,rescue_force_mj_after," +
                "rescue_force_qt_after,quilts_num_before,foods_num_before,water_num_before,bathroom_num_before,showers_num_before," +
                "medical_matters_before,medical_num_before,nurse_num_before,psychologist_before,quilts_num_after,foods_num_after," +
                "water_num_after,bathroom_num_after,showers_num_after,medical_matters_after,medical_num_after,nurse_num_after,psychologist_after," +
                "sickroom_num_before,sickbed_num_before,plasma_num_before,sickroom_num_after,sickbed_num_after,plasma_num_after " +
                "from ypg_result_total where del_flag='0' and task_id=?" +
                " order by cast(sort as int),magnitude");
        List<ResultMainEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResultMainEntity.class),taskId);
        return list;
    }

    @Override
    public List<ResultMainEntityVO> getResultTotalVOByTaskId(String taskId) {
//        String sql = "select * from ypg_result_total where del_flag='0' and task_id=? " +
//                "order by cast(sort as int),magnitude desc";
        String sql = "select * from ypg_result_total where del_flag='0' and task_id=? order by macro_position,magnitude asc";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultMainEntityVO.class), taskId);
    }

    @Override
    public List<ResultMainEntityVO> getResultTotalForReportByTaskId(String taskId) {
        String sql = "select substring(macro_position, 1, strpos(macro_position, ',')+strpos(substring(macro_position, strpos(macro_position, ',')+1), ',')-1) as macroPosition,magnitude,death_toll_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after from ypg_result_total where del_flag='0' and task_id=? group by macroPosition,magnitude,death_toll_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after,substring(macro_position, 1, strpos(macro_position, ',')+strpos(substring(macro_position, strpos(macro_position, ',')+1), ',')-1) order by macroPosition,magnitude";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultMainEntityVO.class), taskId);
    }

    @Override
    public List<ResultMainEntity> getResultTotalForReportMainByTaskId(String taskId) {
        String sql = "select sort,longitude,latitude,macro_position,magnitude,disaster_area,disaster_num,disaster_gdp,death_toll_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after from ypg_result_total where del_flag='0' and task_id=?  order by cast(sort as int),longitude,latitude,macro_position,magnitude";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultMainEntity.class), taskId);
    }

    @Override
    public List<ResultMainEntity> getResultTotalForReportMainByTaskId(List<String> taskIdList) {
        String sql = "select sort,longitude,latitude,macro_position,magnitude,disaster_area,disaster_num,disaster_gdp,death_toll_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after from ypg_result_total where del_flag='0' and task_id in (:taskIdList)  order by cast(sort as int),longitude,latitude,macro_position,magnitude";
        Map idMap = new HashMap();
        idMap.put("taskIdList", taskIdList);
        return namedParameterJdbcTemplate.query(sql, idMap, new BeanPropertyRowMapper<>(ResultMainEntity.class));
    }

    @Override
    public List<ResultMainEntityVO> getResultTotalForReport(String taskId, String province, String city) {
        String sql = "select substring(macro_position, 1, strpos(macro_position, ',')+strpos(substring(macro_position, strpos(macro_position, ',')+1), ',')-1) as macroPosition,magnitude,death_toll_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after from ypg_result_total where del_flag='0' and task_id=? and macro_position like '%"+province+','+city+"%' group by macroPosition,magnitude,death_toll_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after,substring(macro_position, 1, strpos(macro_position, ',')+strpos(substring(macro_position, strpos(macro_position, ',')+1), ',')-1) order by macroPosition,magnitude";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultMainEntityVO.class), taskId);
    }

    @Override
    public List<ResultMainEntityVO> getResultTotalForReport(List<String> taskIdList, String province, String city) {
        String sql = "select substring(macro_position, 1, strpos(macro_position, ',')+strpos(substring(macro_position, strpos(macro_position, ',')+1), ',')-1) as macroPosition,magnitude,death_toll_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after from ypg_result_total where del_flag='0' and task_id in (:taskIdList) and macro_position like '%"+province+','+city+"%' group by macroPosition,magnitude,death_toll_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after,substring(macro_position, 1, strpos(macro_position, ',')+strpos(substring(macro_position, strpos(macro_position, ',')+1), ',')-1) order by macroPosition,magnitude";
        Map idMap = new HashMap();
        idMap.put("taskIdList", taskIdList);
        return namedParameterJdbcTemplate.query(sql, idMap, new BeanPropertyRowMapper<>(ResultMainEntityVO.class));
    }

    @Override
    public List<ResultMainEntityVO> getResultTotalVO(String taskId,String province,String city, String county) {
    String sql =
        "select * from ypg_result_total where del_flag='0' and task_id=? and macro_position like '%"+province+','+city+','+county+"%' "
            + "order by cast(sort as int),magnitude desc";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultMainEntityVO.class), taskId);
    }

    @Override
    public List<ResultMainEntityVO> getResultTotalVO(List<String> taskIdList,String province,String city, String county) {
        String sql =
                "select * from ypg_result_total where del_flag='0' and task_id in (:taskIdList) and macro_position like '%"+province+','+city+','+county+"%' "
                        + "order by cast(sort as int),magnitude desc";
        Map idMap = new HashMap();
        idMap.put("taskIdList", taskIdList);
        return namedParameterJdbcTemplate.query(sql, idMap, new BeanPropertyRowMapper<>(ResultMainEntityVO.class));
    }

    @Override
    public ResultMainEntity findByTaskIdLimit(String taskId) {
        String sql = "select * from ypg_result_total where del_flag='0' and task_id=? limit 1";
        List<ResultMainEntity> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultMainEntity.class), taskId);
        return query.size()<1?null:query.get(0);
    }

    @Override
    public List<ResultMainEntity> exportExcel(String taskId) {
        StringBuilder sql = new StringBuilder("select id,task_id,sort,longitude,latitude,macro_position,magnitude,disaster_area," +
                "disaster_num,disaster_gdp,death_toll_before,del_flag,create_name,create_id,create_time,death_toll_after," +
                "data_source,stretcher_num_before,disaster_loss_before,injured_num_before,resettled_num_before,rescue_force_before,stretcher_num_after," +
                "tent_num_before,disaster_loss_after,injured_num_after,resettled_num_after,rescue_force_after,tent_num_after," +
                "rescue_force_zy_before,rescue_force_mj_before,rescue_force_qt_before,rescue_force_zy_after,rescue_force_mj_after," +
                "rescue_force_qt_after,quilts_num_before,foods_num_before,water_num_before,bathroom_num_before,showers_num_before," +
                "medical_matters_before,medical_num_before,nurse_num_before,psychologist_before,quilts_num_after,foods_num_after," +
                "water_num_after,bathroom_num_after,showers_num_after,medical_matters_after,medical_num_after,nurse_num_after,psychologist_after," +
                "sickroom_num_before,sickbed_num_before,plasma_num_before,sickroom_num_after,sickbed_num_after,plasma_num_after " +
                "from ypg_result_total where del_flag='0' and task_id=?" +
                " order by cast(sort as int),magnitude");
       List<ResultMainEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResultMainEntity.class),taskId);
       return  list;
    }

    @Override
    public void saveRange(String range, String taskId) {
        String sql ="insert into ypg_result_range (task_id,range) values ('"+taskId+"','"+range+"')";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteRange(String taskId) {
        String sql ="delete from ypg_result_range where task_id ='"+taskId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public String getRangeByTaskId(String taskId) {
        String sql="select range from ypg_result_range where task_id= '"+taskId+"'";
        String range = null;
        try {
            range = jdbcTemplate.queryForObject(sql, String.class);
            return range;
        } catch (DataAccessException e) {
            return "0";
        }
    }

}
